package com.ruoyi.generator.controller;

import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.generator.entity.*;
import com.ruoyi.generator.mapper.ContractMapper;
import com.ruoyi.generator.service.ContractService;
import com.ruoyi.generator.service.CustomerService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.velocity.runtime.resource.Resource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.List;

import static com.ruoyi.common.core.domain.AjaxResult.success;
import static com.ruoyi.common.utils.SecurityUtils.getUsername;

@RestController
@RequestMapping("Export")
public class ExportController {

    // TODO: 注入Service依赖
    @Autowired
    private CustomerService customerService;
    @Autowired
    private ContractService  contractService;


    @Autowired
    private com.ruoyi.generator.mapper.ContractMapper ContractMapper;


    /**
     * 导入客户信息
     */
    @Log(title = "导入客户信息", businessType = BusinessType.IMPORT)
    @PostMapping("/importCustomerList")
    public AjaxResult importData(MultipartFile file) throws Exception
    {
        // 预处理Excel文件，确保公式被正确计算
        Workbook processedWorkbook = preprocessExcelFormulas(file.getInputStream());
        
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            processedWorkbook.write(outputStream);
            try (InputStream processedInputStream = new ByteArrayInputStream(outputStream.toByteArray())) {
                ExcelUtil<CustomerInfo> util = new ExcelUtil<CustomerInfo>(CustomerInfo.class);
                List<CustomerInfo> userList = util.importExcel(processedInputStream);
                String msg = customerService.addCustomerList(userList);
                return success(msg);
            }
        } finally {
            if (processedWorkbook != null) {
                processedWorkbook.close();
            }
        }
    }

    /**
     * 导入合同信息
     */
    @Log(title = "导入合同信息", businessType = BusinessType.IMPORT)
    @PostMapping("/importContractList")
    public AjaxResult importContractData(MultipartFile file) throws Exception {
        List<Object> allDataList = new ArrayList<>();
        Class<?>[] entityClasses = {Contract.class, StReturnValue.class, InvoiceForm.class};
        DataFormatter dataFormatter = new DataFormatter();
        Workbook workbook = null;  // 声明在主作用域

        try {
            workbook = new XSSFWorkbook(file.getInputStream());

            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet originalSheet = workbook.getSheetAt(i);

                try (Workbook tempWorkbook = new XSSFWorkbook()) {
                    Sheet tempSheet = tempWorkbook.createSheet(originalSheet.getSheetName());
                    
                    // 创建公式计算器
                    org.apache.poi.ss.usermodel.FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

                    // 单元格复制
                    for (Row srcRow : originalSheet) {
                        Row destRow = tempSheet.createRow(srcRow.getRowNum());
                        for (Cell srcCell : srcRow) {
                            Cell destCell = destRow.createCell(srcCell.getColumnIndex());
                            CellType cellType = srcCell.getCellType();

                            if (cellType == CellType.NUMERIC && DateUtil.isCellDateFormatted(srcCell)) {
                                destCell.setCellValue(srcCell.getDateCellValue());
                                CellStyle dateStyle = tempWorkbook.createCellStyle();
                                dateStyle.cloneStyleFrom(srcCell.getCellStyle());
                                destCell.setCellStyle(dateStyle);
                            } else if (cellType == CellType.NUMERIC) {
                                destCell.setCellValue(srcCell.getNumericCellValue());
                            } else if (cellType == CellType.STRING) {
                                destCell.setCellValue(srcCell.getStringCellValue());
                            } else if (cellType == CellType.BOOLEAN) {
                                destCell.setCellValue(srcCell.getBooleanCellValue());
                            } else if (cellType == CellType.FORMULA) {
                                // 处理公式单元格，计算公式值并设置为具体值
                                try {
                                    org.apache.poi.ss.usermodel.CellValue cellValue = evaluator.evaluate(srcCell);
                                    switch (cellValue.getCellType()) {
                                        case NUMERIC:
                                            if (DateUtil.isCellDateFormatted(srcCell)) {
                                                destCell.setCellValue(DateUtil.getJavaDate(cellValue.getNumberValue()));
                                                CellStyle dateStyle = tempWorkbook.createCellStyle();
                                                dateStyle.cloneStyleFrom(srcCell.getCellStyle());
                                                destCell.setCellStyle(dateStyle);
                                            } else {
                                                destCell.setCellValue(cellValue.getNumberValue());
                                            }
                                            break;
                                        case STRING:
                                            destCell.setCellValue(cellValue.getStringValue());
                                            break;
                                        case BOOLEAN:
                                            destCell.setCellValue(cellValue.getBooleanValue());
                                            break;
                                        case ERROR:
                                            // 如果公式计算出错，设置为空字符串
                                            destCell.setCellValue("");
                                            System.err.println("公式计算出错，位置: 行" + srcRow.getRowNum() + " 列" + srcCell.getColumnIndex() + ", 公式: " + srcCell.getCellFormula());
                                            break;
                                        default:
                                            destCell.setCellValue("");
                                            break;
                                    }
                                } catch (Exception e) {
                                    // 如果公式计算失败，设置为空字符串
                                    destCell.setCellValue("");
                                    System.err.println("公式计算异常，位置: 行" + srcRow.getRowNum() + " 列" + srcCell.getColumnIndex() + ", 公式: " + srcCell.getCellFormula() + ", 异常: " + e.getMessage());
                                }
                            } else if (cellType == CellType.BLANK) {
                                destCell.setBlank();
                            } else {
                                destCell.setCellValue(dataFormatter.formatCellValue(srcCell));
                            }
                        }
                    }

                    ExcelUtil<?> util = new ExcelUtil<>(entityClasses[i]);
                    try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
                        tempWorkbook.write(outputStream);
                        try (InputStream sheetInputStream = new ByteArrayInputStream(outputStream.toByteArray())) {
                            List<?> dataList = util.importExcel(sheetInputStream);
                            allDataList.addAll(dataList);
                        }
                    }
                }  // 自动关闭 tempWorkbook
            }
        } finally {
            // 确保主workbook关闭
            if (workbook != null) {
                workbook.close();
            }
        }

        // 数据验证和处理逻辑
        List<String> errorMessages = new ArrayList<>();
        int successCount = 0;
        int totalCount = allDataList.size();
        
        for (int i = 0; i < allDataList.size(); i++) {
            Object data = allDataList.get(i);
            List<String> rowErrors = new ArrayList<>();
            int rowNumber = i + 1;
            
            try {
                if (data instanceof Contract) {
                    Contract contract = (Contract) data;

                    Contract checkContract = ContractMapper.selectContractByCode(contract.getContractCode());
                    if (checkContract != null) {
                        rowErrors.add("合同编号已存在");
                    }

                    contract.setContractCode(contract.getContractCode().trim());
                    //拆分出合同编号中的前缀
                    String[] parts = contract.getContractCode().split("-");
                    if (parts.length < 2) {
                        rowErrors.add("合同编号格式错误");
                    }
                    // 验证合同必填字段
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(contract.getContractName())) {
                        rowErrors.add("合同名称不能为空");
                    }
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(contract.getContractType())) {
                        rowErrors.add("合同类型不能为空");
                    }
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(contract.getPaymentType())) {
                        rowErrors.add("付款方式不能为空");
                    }
                    if (contract.getContractAmount() == null || contract.getContractAmount() <= 0) {
                        rowErrors.add("合同金额必须大于0");
                    }
                    if (contract.getEffectiveAmount() == null || contract.getEffectiveAmount() <= 0) {
                        rowErrors.add("有效金额必须大于0");
                    }
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(contract.getClientName())) {
                        rowErrors.add("客户名称不能为空");
                    }
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(contract.getManager())) {
                        rowErrors.add("客户经理不能为空");
                    }
                    if (contract.getSignDate() == null) {
                        rowErrors.add("签约时间不能为空");
                    }
                    if (contract.getStartDate() == null) {
                        rowErrors.add("开始时间不能为空");
                    }
                    if (contract.getEndDate() == null) {
                        rowErrors.add("到期时间不能为空");
                    }
                    
                    // 验证日期逻辑
                    if (contract.getSignDate() != null && contract.getStartDate() != null 
                        && contract.getSignDate().after(contract.getStartDate())) {
                        rowErrors.add("签约时间不能晚于开始时间");
                    }
                    if (contract.getStartDate() != null && contract.getEndDate() != null 
                        && contract.getStartDate().after(contract.getEndDate())) {
                        rowErrors.add("开始时间不能晚于到期时间");
                    }
                    
                    if (rowErrors.isEmpty()) {
                        contractService.addContractList(contract);
                        successCount++;
                    }
                    
                } else if (data instanceof StReturnValue) {
                    StReturnValue returnValue = (StReturnValue) data;

                    Contract contract = ContractMapper.selectContractByCode(returnValue.getContractCode());
                    if (contract == null) {
                        rowErrors.add("合同号不存在");
                    }

                    // 验证回款必填字段
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(returnValue.getContractCode())) {
                        rowErrors.add("合同编号不能为空");
                    }
                    if (returnValue.getPaymentDate() == null) {
                        rowErrors.add("回款时间不能为空");
                    }
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(returnValue.getHasInvoice())) {
                        rowErrors.add("是否有发票不能为空");
                    }

                    
                    if (rowErrors.isEmpty()) {
                        contractService.addReturnValueList(returnValue);
                        successCount++;
                    }
                    
                } else if (data instanceof InvoiceForm) {
                    InvoiceForm invoiceForm = (InvoiceForm) data;
                    
                    // 验证发票必填字段
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(invoiceForm.getContractNumber())) {
                        rowErrors.add("合同号不能为空");
                    }
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(invoiceForm.getApplyUnit())) {
                        rowErrors.add("申请单位不能为空");
                    }
                    if (com.ruoyi.common.utils.StringUtils.isEmpty(invoiceForm.getInvoiceType())) {
                        rowErrors.add("发票类型不能为空");
                    }
                    if (invoiceForm.getInvoiceAmount() == null || invoiceForm.getInvoiceAmount() <= 0) {
                        rowErrors.add("发票金额必须大于0");
                    }
                    if (invoiceForm.getStartDate() == null) {
                        rowErrors.add("开始日期不能为空");
                    }
                    if (invoiceForm.getEndDate() == null) {
                        rowErrors.add("结束日期不能为空");
                    }
                    
                    // 验证日期逻辑
                    if (invoiceForm.getStartDate() != null && invoiceForm.getEndDate() != null 
                        && invoiceForm.getStartDate().after(invoiceForm.getEndDate())) {
                        rowErrors.add("开始日期不能晚于结束日期");
                    }
                    
                    // 验证税号格式（如果提供）
                    if (com.ruoyi.common.utils.StringUtils.isNotEmpty(invoiceForm.getTaxNumber())) {
                        if (invoiceForm.getTaxNumber().length() < 15 || invoiceForm.getTaxNumber().length() > 20) {
                            rowErrors.add("税号长度应在15-20位之间");
                        }
                    }
                    
                    // 验证银行账号格式（如果提供）
                    if (com.ruoyi.common.utils.StringUtils.isNotEmpty(invoiceForm.getBankAccount())) {
                        if (invoiceForm.getBankAccount().length() < 10 || invoiceForm.getBankAccount().length() > 25) {
                            rowErrors.add("银行账号长度应在10-25位之间");
                        }
                    }
                    
                    // 验证手机号格式（如果提供）
                    if (com.ruoyi.common.utils.StringUtils.isNotEmpty(invoiceForm.getPhone())) {
                        String phonePattern = "^1[3-9]\\d{9}$";
                        if (!invoiceForm.getPhone().matches(phonePattern)) {
                            rowErrors.add("电话号码格式不正确");
                        }
                    }
                    
                    if (rowErrors.isEmpty()) {
                        contractService.addInvoiceFormList(invoiceForm);
                        successCount++;
                    }
                }
                
                // 如果当前行有错误，记录错误信息
                if (!rowErrors.isEmpty()) {
                    String sheetName = "";
                    if (data instanceof Contract) {
                        sheetName = "合同明细";
                    } else if (data instanceof StReturnValue) {
                        sheetName = "回款明细";
                    } else if (data instanceof InvoiceForm) {
                        sheetName = "发票明细";
                    }
                    errorMessages.add(sheetName + ": 第" + rowNumber + "行数据错误：" + String.join("，", rowErrors));
                }
                
            } catch (Exception e) {
                String sheetName = "";
                if (data instanceof Contract) {
                    sheetName = "合同信息表";
                } else if (data instanceof StReturnValue) {
                    sheetName = "回款信息表";
                } else if (data instanceof InvoiceForm) {
                    sheetName = "发票信息表";
                }
                errorMessages.add(sheetName + "第" + rowNumber + "行数据处理失败：" + e.getMessage());
            }
        }
        
        // 返回处理结果
        if (errorMessages.isEmpty()) {
            return success("导入成功！共处理" + totalCount + "条数据，全部导入成功。");
        } else {
            StringBuilder result = new StringBuilder();
            result.append("数据处理完成！共处理").append(totalCount).append("条数据，")
                  .append("成功导入").append(successCount).append("条，")
                  .append("失败").append(errorMessages.size()).append("条。\n\n");
            result.append("错误详情：\n");
            for (String error : errorMessages) {
                result.append(error).append("\n");
            }
            return success(result.toString());
        }
    }

    /**
     * 模板下载接口
     * 客户信息模板包含字段：
     * 主键ID | 客户编号 | 客户名称 | 省 | 市 | 区 | 详细地址
     * 公司类型 | 公司规模 | 联系人姓名 | 联系人生日 | 联系电话 | 所属行业
     * 客户经理 | 最新跟进记录 | 最后沟通时间 | 客户状态 | 创建者 | 创建时间
     * 更新者 | 更新时间 | 删除标志 | 备注 | 星级重要性 | 纳税人识别号
     */
    @PostMapping("/downloadTemplate")
    public ResponseEntity<byte[]> downloadTemplate() throws IOException {
        // 1. 动态创建Excel工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("客户信息");

        // 创建表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 创建字体样式
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 11);
        headerStyle.setFont(font);

        String[] headers = {
                "客户名称", "省市区", "联系人","手机号", "生日", "客户经理", "详细地址", "联系电话", "公司类型",
                "公司规模", "所属行业", "客户星级", "单位名称", "单位地址", "税号", "联系电话","开户银行","银行账户"
        };

        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
            // 自动调整列宽
            int columnWidth = (headers[i].length() + 4) * 256; // 计算列宽
            sheet.setColumnWidth(i, columnWidth);
        }

        // 添加公司类型下拉列表验证
        int companyColumnIndex = 8; // 根据表头数组位置确定列索引
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint companyConstraint = validationHelper.createFormulaListConstraint(
                "\"国企,私企,政府,个人\""
        );
        // 为单元格添加注释图标
        if (sheet.getRow(0) != null) {
            Cell cell = sheet.getRow(0).getCell(companyColumnIndex);
            if (cell != null) {
                CreationHelper createHelper = workbook.getCreationHelper();
                ClientAnchor anchor = createHelper.createClientAnchor();
                anchor.setCol1(cell.getColumnIndex());
                anchor.setCol2(cell.getColumnIndex() + 1);
                anchor.setRow1(cell.getRowIndex());
                anchor.setRow2(cell.getRowIndex() + 3);

                Comment comment = sheet.createDrawingPatriarch().createCellComment(anchor);
                RichTextString str = createHelper.createRichTextString("可选值：国企/私企/政府/个人");
                comment.setString(str);
                comment.setAuthor("系统提示");
                cell.setCellComment(comment);
            }
        }
        // 4. 转换为字节数组
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        // 5. 构建响应头
        HttpHeaders headersResponse = new HttpHeaders();
        headersResponse.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headersResponse.setContentDispositionFormData("attachment", "customer_template.xlsx");

        return ResponseEntity.ok()
                .headers(headersResponse)
                .body(outputStream.toByteArray());
    }
    /**
     * 导出合同信息模板
     * 模板下载接口
     * templatePath = "sql/合同信息模版.xlsx"
     */
    @PostMapping("/exportContractTemplate")
    public ResponseEntity<byte[]> exportContractTemplate() throws IOException {
        // 1. 读取本地Excel模板文件
        //String templatePath = "sql/合同信息模版.xlsx";
        String templatePath = "/www/wwwroot/shengtai/模版/合同信息模版.xlsx";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        Workbook workbook = new XSSFWorkbook(fileInputStream);

        // 2. 获取现有工作表（假设模板中已包含正确的工作表）
        Sheet sheet = workbook.getSheet("合同信息");

        // 3. 转换为字节数组
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();
        fileInputStream.close();

        // 4. 构建响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", "customer_template.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(outputStream.toByteArray());
    }

    /**
     * 导出客户信息模板
     * 模板下载接口
     * templatePath = "sql/客户信息模版.xlsx"
     */
    @PostMapping("/exportCustomerTemplate")
    public ResponseEntity<byte[]> exportCustomerTemplate() throws IOException {
        // 1. 读取本地Excel模板文件
        //String templatePath = "sql/客户信息模版.xlsx";
        String templatePath = "/www/wwwroot/shengtai/模版/客户信息模版.xlsx";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        
        // 2. 获取现有工作表（假设模板中已包含正确的工作表）
        Sheet sheet = workbook.getSheet("客户信息");
        
        // 3. 转换为字节数组
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();
        fileInputStream.close();
        
        // 4. 构建响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", "customer_template.xlsx");
        
        return ResponseEntity.ok()
                .headers(headers)
                .body(outputStream.toByteArray());
    }

    /**
     * 预处理Excel文件，计算所有公式并转换为具体值
     * 
     * @param inputStream Excel文件输入流
     * @return 处理后的Workbook对象
     * @throws Exception
     */
    private Workbook preprocessExcelFormulas(InputStream inputStream) throws Exception {
        Workbook originalWorkbook = null;
        Workbook processedWorkbook = null;
        
        try {
            originalWorkbook = new XSSFWorkbook(inputStream);
            processedWorkbook = new XSSFWorkbook();
            
            // 创建公式计算器
            org.apache.poi.ss.usermodel.FormulaEvaluator evaluator = originalWorkbook.getCreationHelper().createFormulaEvaluator();
            DataFormatter dataFormatter = new DataFormatter();
            
            // 处理每个工作表
            for (int sheetIndex = 0; sheetIndex < originalWorkbook.getNumberOfSheets(); sheetIndex++) {
                Sheet originalSheet = originalWorkbook.getSheetAt(sheetIndex);
                Sheet processedSheet = processedWorkbook.createSheet(originalSheet.getSheetName());
                
                // 处理每一行
                for (Row originalRow : originalSheet) {
                    Row processedRow = processedSheet.createRow(originalRow.getRowNum());
                    
                    // 处理每个单元格
                    for (Cell originalCell : originalRow) {
                        Cell processedCell = processedRow.createCell(originalCell.getColumnIndex());
                        CellType cellType = originalCell.getCellType();
                        
                        if (cellType == CellType.NUMERIC && DateUtil.isCellDateFormatted(originalCell)) {
                            processedCell.setCellValue(originalCell.getDateCellValue());
                            CellStyle dateStyle = processedWorkbook.createCellStyle();
                            dateStyle.cloneStyleFrom(originalCell.getCellStyle());
                            processedCell.setCellStyle(dateStyle);
                        } else if (cellType == CellType.NUMERIC) {
                            processedCell.setCellValue(originalCell.getNumericCellValue());
                        } else if (cellType == CellType.STRING) {
                            processedCell.setCellValue(originalCell.getStringCellValue());
                        } else if (cellType == CellType.BOOLEAN) {
                            processedCell.setCellValue(originalCell.getBooleanCellValue());
                        } else if (cellType == CellType.FORMULA) {
                            // 处理公式单元格，计算公式值并设置为具体值
                            try {
                                org.apache.poi.ss.usermodel.CellValue cellValue = evaluator.evaluate(originalCell);
                                switch (cellValue.getCellType()) {
                                    case NUMERIC:
                                        if (DateUtil.isCellDateFormatted(originalCell)) {
                                            processedCell.setCellValue(DateUtil.getJavaDate(cellValue.getNumberValue()));
                                            CellStyle dateStyle = processedWorkbook.createCellStyle();
                                            dateStyle.cloneStyleFrom(originalCell.getCellStyle());
                                            processedCell.setCellStyle(dateStyle);
                                        } else {
                                            processedCell.setCellValue(cellValue.getNumberValue());
                                        }
                                        break;
                                    case STRING:
                                        processedCell.setCellValue(cellValue.getStringValue());
                                        break;
                                    case BOOLEAN:
                                        processedCell.setCellValue(cellValue.getBooleanValue());
                                        break;
                                    case ERROR:
                                        // 如果公式计算出错，设置为空字符串
                                        processedCell.setCellValue("");
                                        System.err.println("公式计算出错，位置: 行" + originalRow.getRowNum() + " 列" + originalCell.getColumnIndex() + ", 公式: " + originalCell.getCellFormula());
                                        break;
                                    default:
                                        processedCell.setCellValue("");
                                        break;
                                }
                            } catch (Exception e) {
                                // 如果公式计算失败，设置为空字符串
                                processedCell.setCellValue("");
                                System.err.println("公式计算异常，位置: 行" + originalRow.getRowNum() + " 列" + originalCell.getColumnIndex() + ", 公式: " + originalCell.getCellFormula() + ", 异常: " + e.getMessage());
                            }
                        } else if (cellType == CellType.BLANK) {
                            processedCell.setBlank();
                        } else {
                            processedCell.setCellValue(dataFormatter.formatCellValue(originalCell));
                        }
                    }
                }
            }
            
            return processedWorkbook;
            
        } finally {
            if (originalWorkbook != null) {
                originalWorkbook.close();
            }
        }
    }
}